* clean rental price data from Ministerio de Fomento
********************************************************************************
* prelims
********************************************************************************
{
	
clear all
cap log close
set more off

}
********************************************************************************
* clean data
********************************************************************************
{
* aggregate level
foreach agglevel in csec { 

	if "`agglevel'" == "csec" {
		local excel SeccionesCensales
		local aggvars CPRO CUDMU CUSEC
	}

* load data
import excel "$orig/mfomento/Sistema_Indice_Alquiler_Vivienda.xlsx", sheet("`excel'") firstrow allstring clear

* BI_ALVHEPCO_T_: Recuento subconjunto alquiler Grupo GGT01 VU (count)
* ALQM2mes: Alquiler mensual m2
* ALQTBID12: ALQTBID12
* SLVM2: Superficie m2
foreach vartype in ALQTBID12 SLVM2 {
	foreach ht in VC VU {
		foreach year in 15 16 17 18 {
			foreach pct in M 25 75 {

			* store variable name
			local var `vartype'_`pct'_`ht'_`year' 	
			* remove "." if thousands
			replace `var' = subinstr(`var', ".", "",.) 

			}
		}
	}
}

* destring vars
destring BI_ALVHEPCO_TVC_15-SLVM2_75_VU_18, force replace

* reshape long
local vars

foreach vartype in ALQTBID12 ALQM2mes_LV SLVM2 BI_ALVHEPCO_T {

	foreach ht in VC VU {

		if "`vartype'"	!= 	"BI_ALVHEPCO_T" {
		foreach pct in M 25 75 {
			local vars `vars' `vartype'_`pct'_`ht'_

			}
		}	
			
		if "`vartype'"	== 	"BI_ALVHEPCO_T" {
			local vars `vars' `vartype'`ht'_ 
		}
	
	}
}

* reshape
reshape long `vars', i(`aggvars') j(year)

* rename
foreach vartype in ALQTBID12 ALQM2mes_LV SLVM2 BI_ALVHEPCO_T {

	foreach ht in VC VU {

		if "`vartype'"	!= 	"BI_ALVHEPCO_T" {
		foreach pct in M 25 75 {
			rename `vartype'_`pct'_`ht'_ `vartype'_`pct'_`ht'
			la var `vartype'_`pct'_`ht' "`vartype' Pct `pct' Htype `ht'"

			}
		}	
	
		if "`vartype'"	== 	"BI_ALVHEPCO_T" {
			rename `vartype'`ht'_  `vartype'`ht'
			la var `vartype'`ht' "Count HType `ht'"
		}
	}
}

* year
replace year = year + 2000
la var year "year"

* identifiers
g CMUN = substr(CUDMUN,3,3)
g CDIS = substr(CUSEC,6,2)
g CSEC = substr(CUSEC,8,3)
la var CMUN "Municipality"
la var CDIS "District"
la var CSEC "Census Tract"

* store
order CPRO CMUN CDIS CSEC
compress
save "$data/int/rental_price_`agglevel'.dta", replace

} 
}
********************************************************************************
* closing
********************************************************************************
{
	
cap log close
clear

}
